Kester Weather Station

Author

Grant and Neil Kester

Published

November 21, 2023

version: 0.3.2

We are trying to improve our charting work with Observable JS.

Update Note: We have fixed the x-axis labels by properly converting to and dealing with date objects.

This page displays the measurements taken from our personal weather station. The raw measurements are taken every 15 minutes but we have aggregated the information when pulling it from our PostgreSQL database to every hour, day, and month.

Much improvement is required to make meaningful conclusions from the data and for them to make sense in the context of what the measurements mean.

Charting

Show the code
Plot.dot(filteredData,
  {
    x: "date",
    y: "measure", 
    stroke: "measure",
    tip: true
  })
  .plot()

The plot as a line graph.

Show the code
Plot.plot({
  marks: [
    Plot.line(filteredData, 
      {
        x: "date",
        y: "measure"
      })
    ]
})

Filtered Data Table

Show the code
Inputs.table(filteredData)

Appendix

Find the source code on GitHub at Kester Weather Visualization Site

Show the code
# This function builds the PostgreSQL query required to aggregate the timeseries
#  data to a specified level.
agg_query <- function(agg_function, type, agg_level){
  
  if(agg_level == 'As Measured'){

    query <- sprintf("SELECT time AS date, 
                             type, 
                             \"measurementValue\" AS measure, 
                             \"measurementValue\" AS measure_min,
                             \"measurementValue\" AS measure_max 
                      FROM sensor_data 
                      WHERE type = '%s'",
                      type)
    
  }else{
    
    query <- sprintf("SELECT date, 
                             type, 
                             %s(\"measurementValue\") AS measure,
                             min(\"measurementValue\") AS measure_min,
                             max(\"measurementValue\") AS measure_max 
                      FROM (
                      SELECT date_trunc('%s',time) AS date,
                             type,
                             \"measurementValue\" 
                      FROM sensor_data
                      WHERE type = '%s') AS A 
                      GROUP BY date,
                               type",
            agg_function,
            agg_level,
            type)
    
  }

  return(query)
  
}

measure_gather <- function(con, measure_spec, measure_tib, agg_levels){
  
  for(measure in 1:nrow(measure_spec)){
    
    for(agg in agg_levels){
      
      if(agg == 'As Measured'){
        
        temp <- DBI::dbGetQuery(conn = con,
                                statement = agg_query(agg_function = measure_spec[measure,1],
                                                      type = measure_spec[measure,2],
                                                      agg_level = agg))
        
        measure_tib <- dplyr::bind_rows(measure_tib,
                                        tibble::tibble(aggregate_level = agg,
                                                       temp))
        
      }else{
        
        temp <- DBI::dbGetQuery(conn = con,
                                statement = agg_query(agg_function = measure_spec[measure,1],
                                                      type = measure_spec[measure,2],
                                                      agg_level = agg))
        
        measure_tib <- dplyr::bind_rows(measure_tib,
                                        tibble::tibble(aggregate_level = agg,
                                                       temp))
        
      }
      
    }
    
  }
  
  return(measure_tib)
  
}
Show the code
if(update){
  
  load(file = "./connectionInfo.RData")
  
  measure_spec <- tibble::tibble(fun = c('avg',
                                         'avg',
                                         'avg',
                                         'avg',
                                         'sum',
                                         'avg',
                                         'avg',
                                         'avg'),
                                 type = c('Air Humidity',
                                          'Air Temperature',
                                          'Barometric Pressure',
                                          'Light Intensity',
                                          'Rain Gauge',
                                          'UV Index',
                                          'Wind Direction Sensor',
                                          'Wind Speed'))
  
  agg_levels <- c('As Measured','month','day','hour')
  
  measure_tib <- tibble::tibble(aggregate_level = "NA",
                                date = Sys.time(),
                                type = "NA",
                                measure = 1.1,
                                measure_min = 1.1,
                                measure_max = 1.1)[-1,]
  
  con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
                        dbname = db,
                        host = host,
                        port = port,
                        user = user,
                        password = password)
  
  measure_tib <- measure_gather(con = con,
                                measure_spec = measure_spec,
                                measure_tib = measure_tib,
                                agg_levels = agg_levels)
  
  DBI::dbDisconnect(conn = con)
  
  save(measure_tib,
       file = "./weatherData.RDS")
  
  rm(con,db,host,password,port,user)
}

if(file.exists("./weatherData.RDS")){
  
  load(file = "./weatherData.RDS")
  
}

if(exists(x = "measure_tib")){
  
  ojs_define(measures = measure_tib)
  
}
Show the code
import { aq, op } from '@uwdata/arquero'
d3 = require("d3@7")
parser = d3.timeParse("%Y-%m-%d %H:%M:%S");
Show the code
measures_trans = aq.from(transpose(measures)).derive({ date: aq.escape(d => parser(d.date)) })

filteredData = measures_trans
  .params({
    m: measure_type,
    t: time_aggregation
  })
  .filter((d,p) => op.includes(d.type, p.m) && op.includes(d.aggregate_level, p.t))

Personal Website